package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.jhc.bean.UserBean;

public class NewUserDao {
	
	private Connection con = null;
	int PageCount;
	
	public void addNewUser( String name, String password,String displayname,String email,String phone) {
		String sql = "insert into user(name,password,displayname,email,phone,date) "
				+ "values(?,password(?),?,?,?,now())";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);			
			st.setString(1,name);
			st.setString(2, password);
			st.setString(3, displayname);
			st.setString(4,email);
			st.setString(5,phone);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public ArrayList<UserBean> getAllUser(int page,int titleCount) {
		ArrayList<UserBean> list = new ArrayList<UserBean>();
		String sql = "select id,name,displayname,email,phone,date from user limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);	
			while (result.next()) {
				UserBean u = new UserBean();
				u.setId(result.getInt(1));
				u.setName(result.getString(2));
				u.setDisplayname(result.getString(3));
				u.setEmail(result.getString(4));
				u.setPhone(result.getString(5));
				u.setDate(result.getDate(6));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public int getUserCount(int titleCount) {
		String sql = "select count(*) from user";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	
	public void DeleteUser(int id) {
		String sql = "delete from user where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void setCon(Connection con) {
		this.con = con;
	}

}
